from sqlalchemy import create_engine, Column, Integer, String, Float, Date, DateTime, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime, date
from models import UserRole,  BorrowStatus
from config import db_config, default_data

# 数据库配置
DATABASE_URL = db_config.get_database_url()

print(DATABASE_URL)

# 创建引擎，添加更多配置选项
engine = create_engine(
    DATABASE_URL, 
    echo=False,  # 设置为True可以看到SQL日志
    pool_pre_ping=True  # 检查连接是否有效
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# 数据库模型
class DBUser(Base):
    """用户数据库模型"""
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True, nullable=False)
    name = Column(String(100), nullable=False)
    password = Column(String(100), nullable=False)  # 改为明文密码
    role = Column(Enum(UserRole), default=UserRole.USER)
    created_at = Column(DateTime, default=datetime.utcnow)

class DBBookBorrow(Base):
    """图书借阅数据数据库模型"""
    __tablename__ = "book_borrow_data"
    
    id = Column(Integer, primary_key=True, index=True)
    borrow_date = Column(Date, nullable=False, index=True)
    borrower = Column(String(100), nullable=False, index=True)
    borrowed_book = Column(String(200), nullable=False, index=True)
    book_unit_price = Column(Float, nullable=False)
    borrow_status = Column(Enum(BorrowStatus), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

def get_db():
    """获取数据库会话"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

def init_db():
    """初始化数据库"""
    print("开始初始化数据库...")
    print(f"当前数据库类型: MySQL")
    print(f"数据库URL: {DATABASE_URL}")
    
    try:
        # 创建所有表（如果不存在的话）
        Base.metadata.create_all(bind=engine)
        print("数据库表结构检查/创建完成")
        
        # 创建默认用户和测试数据
        db = SessionLocal()
        try:
            # 检查是否已有管理员用户
            admin_user = db.query(DBUser).filter(DBUser.username == default_data.DEFAULT_ADMIN_USERNAME).first()
            if not admin_user:
                print("创建默认管理员用户...")
                admin_user = DBUser(
                    username=default_data.DEFAULT_ADMIN_USERNAME,
                    name=default_data.DEFAULT_ADMIN_NAME,
                    password=default_data.DEFAULT_ADMIN_PASSWORD,  # 直接使用明文密码
                    role=UserRole.ADMIN
                )
                db.add(admin_user)
                print(f"管理员用户创建完成 (用户名: {default_data.DEFAULT_ADMIN_USERNAME}, 密码: {default_data.DEFAULT_ADMIN_PASSWORD})")
            else:
                print("管理员用户已存在")
            
            # 检查是否已有普通用户
            normal_user = db.query(DBUser).filter(DBUser.username == default_data.DEFAULT_USER_USERNAME).first()
            if not normal_user:
                print("创建默认普通用户...")
                normal_user = DBUser(
                    username=default_data.DEFAULT_USER_USERNAME,
                    name=default_data.DEFAULT_USER_NAME,
                    password=default_data.DEFAULT_USER_PASSWORD,  # 直接使用明文密码
                    role=UserRole.USER
                )
                db.add(normal_user)
                print(f"普通用户创建完成 (用户名: {default_data.DEFAULT_USER_USERNAME}, 密码: {default_data.DEFAULT_USER_PASSWORD})")
            else:
                print("普通用户已存在")
            
            db.commit()
            
            # 创建示例图书借阅数据
            create_sample_book_borrow_data(db)
            
            print("数据库初始化完成！")
            
        except Exception as e:
            db.rollback()
            print(f"初始化数据库失败: {e}")
            raise
        finally:
            db.close()
            
    except Exception as e:
        print(f"数据库初始化过程中发生错误: {e}")
        raise

def create_sample_book_borrow_data(db):
    """创建示例图书借阅数据"""
    # 检查是否已有数据
    existing_data = db.query(DBBookBorrow).first()
    if existing_data:
        print("图书借阅数据已存在，跳过示例数据创建")
        return
    
    print("创建示例图书借阅数据...")
    import random
    from datetime import timedelta
    
    borrowers = ["张三", "李四", "王五", "赵六", "钱七", "孙八"]
    books = ["《Python编程》", "《数据结构》", "《算法导论》", "《数据库原理》", "《计算机网络》", "《操作系统》"]
    borrow_statuses = [BorrowStatus.BORROWED, BorrowStatus.RETURNED, BorrowStatus.OVERDUE, BorrowStatus.LOST]
    
    # 生成最近N天的数据（根据配置）
    today = date.today()
    total_records = 0
    
    for i in range(default_data.SAMPLE_DATA_DAYS):
        current_date = today - timedelta(days=i)
        
        # 为每个借阅者生成数据
        for borrower in borrowers:
            # 随机选择书籍和状态
            book = random.choice(books)
            status = random.choice(borrow_statuses)
            price = round(random.uniform(20.0, 100.0), 2)
            
            borrow_data = DBBookBorrow(
                borrow_date=current_date,
                borrower=borrower,
                borrowed_book=book,
                book_unit_price=price,
                borrow_status=status
            )
            db.add(borrow_data)
            total_records += 1
    
    try:
        db.commit()
        print(f"示例图书借阅数据创建成功！共创建 {total_records} 条记录")
    except Exception as e:
        db.rollback()
        print(f"创建示例数据失败: {e}")
        raise

if __name__ == "__main__":
    init_db()
    print("数据库初始化完成")